﻿using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using Mono.Data.Sqlite;

namespace BToolkit
{
	/// <summary>
	/// 数据库操作助手（一个数据库new一个新实例）
	/// </summary>
	public class SQLiteHelper
	{
		public const string Key_CreateTime = "create_time";

		/// <summary>
		/// 数据库目录
		/// </summary>
		private static string DbPath;
		private string DbName;
		private string DbPassword;

		/// <summary>
		/// 创建一个新的数据库管理助手（一个库对应一个实例）
		/// </summary>
		public SQLiteHelper(string filePath, string dbName, string password = null)
		{
			DbPath = filePath;
			DbName = dbName;
			DbPassword = password;
			try
			{
				var fullPath = Path.Combine(DbPath, DbName);
				SqliteConnection conn = new SqliteConnection("Data Source=" + fullPath);
				conn.Open();
				if (!string.IsNullOrEmpty(DbPassword))
				{
					conn.ChangePassword(DbPassword);
				}
			}
			catch
			{
				Console.WriteLine("数据库已存在");
			}
		}

		/// <summary>
		/// 判断表是否存在
		/// </summary>
		/// <returns></returns>
		public bool IsContainsTable(string tableName)
		{
			var fullPath = Path.Combine(DbPath, DbName);
			SqliteConnection conn = new SqliteConnection("data source=" + fullPath);
			CheckOpen(conn);
			string sql = "select count(*) from sqlite_master where type='table' and name='" + tableName + "'";
			SqliteCommand cmd = new SqliteCommand(sql, conn);
			int count = Convert.ToInt32(cmd.ExecuteScalar());
			return count > 0;
		}

		/// <summary>
		/// 创建表(自动添加时间字段并启用默认值)
		/// </summary>
		public void CreateTable(string tableName, Dictionary<string, Type> keysAndTypes)
		{
            if (keysAndTypes.ContainsKey(Key_CreateTime))
            {
				keysAndTypes.Remove(Key_CreateTime);
            }
			var fullPath = Path.Combine(DbPath, DbName);
			SqliteConnection conn = new SqliteConnection("data source=" + fullPath);
			CheckOpen(conn);
			string keysStr = "id integer primary key autoincrement";
			foreach (var item in keysAndTypes)
			{
				keysStr += "," + item.Key + " " + TypeToString(item.Value);
				string defaultValue = GetDefaultValue(item.Value);
				if (!string.IsNullOrEmpty(defaultValue))
				{
					keysStr += " default " + GetDefaultValue(item.Value);
				}
			}
			keysStr += "," + Key_CreateTime + " datetime default(datetime('now','localtime'))";
			string sql = "create table if not exists " + tableName + "(" + keysStr + ")";
			SqliteCommand cmd = new SqliteCommand(sql, conn);
			cmd.ExecuteNonQuery();
			conn.Close();
		}

		/// <summary>
		/// 删除表
		/// </summary>
		public void DeleteTable(string tableName)
		{
			var fullPath = Path.Combine(DbPath, DbName);
			SqliteConnection conn = new SqliteConnection("data source=" + fullPath);
			CheckOpen(conn);
			string sql = "drop table if exists " + tableName;
			SqliteCommand cmd = new SqliteCommand(sql, conn);
			cmd.ExecuteNonQuery();
			conn.Close();
		}

		/// <summary>
		/// 判断行是否存在
		/// </summary>
		/// <returns></returns>
		public bool IsContainsRow(string tableName, string whereKey, object whereValue)
		{
			DataTable dataTable = SelectData(tableName, whereKey, whereValue, null, null, null, 0, 0, 1);
			return dataTable.Rows.Count > 0;
		}

		/// <summary>
		/// 插入数据(枚举必须先转成Int)
		/// </summary>
		public bool InsertData(string tableName, Dictionary<string, object> keysAndValues)
		{
			var fullPath = Path.Combine(DbPath, DbName);
			SqliteConnection conn = new SqliteConnection("data source=" + fullPath);
			CheckOpen(conn);
			//准备好key
			string keysStr = "";
			foreach (var item in keysAndValues)
			{
				if (item.Value != null)
				{
					keysStr += "," + item.Key;
				}
			}
			if (keysStr.StartsWith(","))
			{
				keysStr = keysStr.Substring(1, keysStr.Length - 1);
			}
			else
			{
				Console.WriteLine(">>>>>>>>>>>插入数据失败：没有输入的值");
				return false;
			}
			//准备好value
			string valuesStr = "";
			foreach (var item in keysAndValues)
			{
				if (item.Value != null)
				{
					if (item.Value.GetType() == typeof(string))
					{
						valuesStr += ",'" + item.Value + "'";
					}
					else
					{
						valuesStr += "," + item.Value;
					}
				}
			}
			valuesStr = valuesStr.Substring(1, valuesStr.Length - 1);
			string sql = "insert into " + tableName + "(" + keysStr + ") values(" + valuesStr + ")";
			SqliteCommand cmd = new SqliteCommand(sql, conn);
			cmd.ExecuteNonQuery();
			conn.Close();
			return true;
		}

		/// <summary>
		/// 查询第一个符合的数据
		/// </summary>
		public DataRow SelectOneData(string tableName, string whereKey = null, object whereValue = null, string orderDescByKey = null)
		{
			DataTable datas = SelectData(tableName, whereKey, whereValue, orderDescByKey, null, null, 0, 0, 1);
			if (datas != null && datas.Rows.Count > 0)
			{
				return datas.Rows[0];
			}
			else
			{
				return null;
			}
		}

		/// <summary>
		/// 查询数据(page和pageSize其中一个为0则只会查询一条数据)
		/// </summary>
		public DataTable SelectData(string tableName, string whereKey = null, object whereValue = null, string formDate = null, string toDate = null, string orderDescByKey = null, int page = 0, int pageSize = 0, int getCount = 0)
		{
			if (!IsContainsTable(tableName))
			{
				Debuger.LogError("表[" + tableName + "]不存在");
				return null;
			}
			DataTable dataTable = new DataTable();
			var fullPath = Path.Combine(DbPath, DbName);
			using (SqliteConnection conn = new SqliteConnection("data source=" + fullPath))
			{
				CheckOpen(conn);
				string sql = "select * from " + tableName;
				if (!string.IsNullOrEmpty(whereKey) && whereValue != null)
				{
					sql += " where " + whereKey + "='" + whereValue + "'";
				}
				if (!string.IsNullOrEmpty(formDate) && !string.IsNullOrEmpty(toDate))
				{
					if (sql.Contains("where"))
					{
						sql += " and";
					}
					else
					{
						sql += " where";
					}
					sql += " create_time between '" + formDate + "' and '" + toDate + "'";
				}
				if (!string.IsNullOrEmpty(orderDescByKey))
				{
					sql += " order by " + orderDescByKey + " desc";
				}
				if (getCount > 0)
				{
					sql += " limit " + getCount;
				}
				else if (page > 0 && pageSize > 0)
				{
					sql += " limit " + ((page - 1) * pageSize) + "," + pageSize;
				}
				//Debuger.LogError(sql);
				SqliteCommand command = new SqliteCommand(conn);
				command.CommandText = sql;
				SqliteDataReader reader = command.ExecuteReader();
				dataTable = SqliteDataReaderToDataTable(reader);
				reader.Close();
				conn.Close();
			}
			return dataTable;
		}

		/// <summary>
		/// 查询数据条数(page和pageSize其中一个为0则只会查询一条数据)
		/// </summary>
		public int GetRecordsAmount(string tableName, string whereKey = null, object whereValue = null, string formDate = null, string toDate = null, string orderDescByKey = null, int page = 0, int pageSize = 0, int getCount = 0)
		{
			int amount = 0;
			if (!IsContainsTable(tableName))
			{
				Debuger.LogError("表[" + tableName + "]不存在");
				return 0;
			}
			var fullPath = Path.Combine(DbPath, DbName);
			using (SqliteConnection conn = new SqliteConnection("data source=" + fullPath))
			{
				CheckOpen(conn);
				string sql = "select count(id) from " + tableName;
				if (!string.IsNullOrEmpty(whereKey) && whereValue != null)
				{
					sql += " where " + whereKey + "='" + whereValue + "'";
				}
				if (!string.IsNullOrEmpty(formDate) && !string.IsNullOrEmpty(toDate))
				{
					if (sql.Contains("where"))
					{
						sql += " and";
					}
					else
					{
						sql += " where";
					}
					sql += " create_time between '" + formDate + "' and '" + toDate + "'";
				}
				if (!string.IsNullOrEmpty(orderDescByKey))
				{
					sql += " order by " + orderDescByKey + " desc";
				}
				if (getCount > 0)
				{
					sql += " limit " + getCount;
				}
				else if (page > 0 && pageSize > 0)
				{
					sql += " limit " + ((page - 1) * pageSize) + "," + pageSize;
				}
				//Debuger.LogError(sql);
				SqliteCommand command = new SqliteCommand(conn);
				command.CommandText = sql;
				amount = Convert.ToInt32(command.ExecuteScalar());
				conn.Close();
			}
			return amount;
		}

		/// <summary>
		/// 根据条件获取下一条数据
		/// </summary>
		public DataRow SelectNextData(string tableName, string whereKey, object whereValue)
		{
			if (!IsContainsTable(tableName))
			{
				Debuger.LogError("表[" + tableName + "]不存在");
				return null;
			}
			DataTable dataTable = new DataTable();
			var fullPath = Path.Combine(DbPath, DbName);
			using (SqliteConnection conn = new SqliteConnection("data source=" + fullPath))
			{
				CheckOpen(conn);
				string sql = "select * from " + tableName + " where " + whereKey + "='" + whereValue + "' limit 1";
				SqliteCommand command = new SqliteCommand(conn);
				command.CommandText = sql;
				SqliteDataReader reader = command.ExecuteReader();
				if (reader.Read())
				{
					long id = (long)reader["id"];
					sql = "select * from " + tableName + " where id > " + id + " limit 1";
					command = new SqliteCommand(conn);
					command.CommandText = sql;
					reader = command.ExecuteReader();
					dataTable = SqliteDataReaderToDataTable(reader);
					if (dataTable.Rows.Count == 0)
					{
						sql = "select * from " + tableName + " limit 1";
						command = new SqliteCommand(conn);
						command.CommandText = sql;
						reader = command.ExecuteReader();
						dataTable = SqliteDataReaderToDataTable(reader);
					}
				}
				else
				{
					sql = "select * from " + tableName + " limit 1";
					command = new SqliteCommand(conn);
					command.CommandText = sql;
					reader = command.ExecuteReader();
					dataTable = SqliteDataReaderToDataTable(reader);
				}
				reader.Close();
				conn.Close();
				if (dataTable.Rows.Count > 0)
				{
					return dataTable.Rows[0];
				}
				return null;
			}
		}

		/// <summary>
		/// 修改数据
		/// </summary>
		public void UpdateData(string tableName, string whereKey, object whereValue, Dictionary<string, object> keysAndValues)
		{
			var fullPath = Path.Combine(DbPath, DbName);
			SqliteConnection conn = new SqliteConnection("data source=" + fullPath);
			CheckOpen(conn);
			string keyValues = "";
			foreach (var item in keysAndValues)
			{
				if (item.Value.GetType() == typeof(string))
				{
					keyValues += "," + item.Key + "='" + item.Value + "'";
				}
				else
				{
					keyValues += "," + item.Key + "=" + item.Value;
				}
			}
			keyValues = keyValues.Substring(1, keyValues.Length - 1);
			string sql = "update " + tableName + " set " + keyValues + " where " + whereKey + " = '" + whereValue + "'";
			SqliteCommand cmd = new SqliteCommand(sql, conn);
			cmd.ExecuteNonQuery();
			conn.Close();
		}

		/// <summary>
		/// 删除数据
		/// </summary>
		public void DeleteData(string tableName, string key, object value)
		{
			var fullPath = Path.Combine(DbPath, DbName);
			SqliteConnection conn = new SqliteConnection("data source=" + fullPath);
			CheckOpen(conn);
			string sql = "delete from " + tableName + " where " + key + " = '" + value + "'";
			SqliteCommand cmd = new SqliteCommand(sql, conn);
			cmd.ExecuteNonQuery();
			conn.Close();
		}

		/// <summary>
		/// 检查打开状态
		/// </summary>
		private void CheckOpen(SqliteConnection conn)
		{
			if (conn.State != ConnectionState.Open)
			{
				if (!string.IsNullOrEmpty(DbPassword))
				{
					conn.SetPassword(DbPassword);
				}
				conn.Open();
			}
		}

		/// <summary>
		/// 类型转sql语句
		/// </summary>
		private string TypeToString(Type type)
		{
			if (type == typeof(string))
			{
				return "text";
			}
			else if (type == typeof(int))
			{
				return "int";
			}
			else if (type == typeof(long))
			{
				return "integer";
			}
			else if (type == typeof(float) || type == typeof(double))
			{
				return "real";
			}
			else if (type == typeof(bool))
			{
				Console.WriteLine(">>>>>>>>>>>>>>出错了：不支持bool类型存储，请使用int代替");
				return "integer";
			}
			return "bolb";//完全根据它的输入存储，二进制可用
		}

		/// <summary>
		/// 类型转sql语句
		/// </summary>
		private string GetDefaultValue(Type type)
		{
			if (type == typeof(string))
			{
				return "";
			}
			else if (type == typeof(int) || type == typeof(float) || type == typeof(double) || type == typeof(bool))
			{
				return "0";
			}
			return null;
		}

		private DataTable SqliteDataReaderToDataTable(SqliteDataReader reader)
		{
			DataTable dataTable = new DataTable();
			int colCount = reader.FieldCount;
			for (int i = 0; i < colCount; i++)
			{
				dataTable.Columns.Add(reader.GetName(i), reader.GetFieldType(i));
			}
			dataTable.BeginLoadData();
			object[] objValues = new object[colCount];
			while (reader.Read())
			{
				reader.GetValues(objValues);
				dataTable.LoadDataRow(objValues, true);
			}
			dataTable.EndLoadData();
			return dataTable;
		}
	}
}
